package com.example.goods_service.dao;

import com.example.goods_service.vo.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;


@Repository
@Mapper
public interface GoodsDao_zhoufuyu {
    /*-----------------------------------------------根据id查询商品类目--------------------------------------------------------*/
    //查最小的
    @Select("select category_name from t_commodity_category where id = #{id}")
    @Results(
            @Result(property = "name", column = "category_name")
    )
    T_commodity_category sel_smallfenlei(long id);

    //查第二层
    @Select("select category_name from t_commodity_category where id = (select category_parent_ID from t_commodity_category where id = #{id})")
    //数据库实体列名与实体类属性字段不相同时要映射
    @Results(
            @Result(property = "name", column = "category_name")
    )
    T_commodity_category sel_middlefenlei(long id);

    //查最大的
    @Select("select category_name from t_commodity_category where category_parent_ID = 0 and id = (select category_parent_ID from t_commodity_category where id = (select category_parent_ID from t_commodity_category where id = #{id}))")
    @Results(
            @Result(property = "name", column = "category_name")
    )
    T_commodity_category sel_bigfenlei(long id);

    /*-----------------------------------------------根据id查询商品属性--------------------------------------------------------*/
    @Select("select b.attribute,a.details from t_attribute_details a,\n" +
            "t_category_attribute b,\n" +
            "t_commodity c,\n" +
            "t_selected_attribute d\n" +
            "where\n" +
            "d.goods_id=c.id and\n" +
            "a.id=d.attribute_details_id and\n" +
            "b.id=d.attribute_id and\n" +
            "c.id=#{id}")
    List<T_attribute> sel_attribute(int id);

    /*-----------------------------------------------根据id查询商品详情--------------------------------------------------------*/
    @Select("select distinct b.configuration_type,a.config_details from t_config_details a,\n" +
            "t_category_config b,\n" +
            "t_commodity c,\n" +
            "t_selected_config d\n" +
            "where\n" +
            "d.goods_id=c.id and\n" +
            "a.id=d.config_details_id and\n" +
            "b.id=d.config_id and\n" +
            "c.id=#{id}")
    List<T_config> sel_config(int id);

    //根据id查询商品表中商品category_id
    @Select("select category_id from t_commodity where id = #{id}")
    T_commodity selcom_id(int id);


    /*-----------------------------------------------根据id查询展示到修改界面所用方法--------------------------------------------------------*/
    @Select("select distinct a.commodity_name,a.sales_volume,a.commodity_price,c.picture_path_name,a.commodity_description,b.price,b.stock from \n" +
            "t_commodity a inner join \n" +
            "t_commodity_allocation b inner join \n" +
            "t_picture c inner join\n" +
            "t_commodity_picture d\n" +
            "on \n" +
            "a.id=b.commodity_id  and d.commodity_id = b.commodity_id and c.id = d.picture_id\n" +
            "where a.id= #{id} ")
    @Results({
            @Result(property = "commodity_name", column = "commodity_name"),
            @Result(property = "commodity_description", column = "commodity_description"),
            @Result(property = "commodity_price", column = "commodity_price"),
            @Result(property = "sales_volume", column = "sales_volume"),
            @Result(property = "commodity_picture", column = "picture_path_name"),
            @Result(property = "price", column = "price"),
            @Result(property = "stock", column = "stock")
    })
    List<T_com_com_all> selbyid(int id);

    /*-------------------------------------修改商品名称、描述、价格----------------------------------------------*/
    @Update("update t_commodity set commodity_name=#{commodity_name},commodity_description=#{commodity_description},commodity_price=#{commodity_price} where id=#{id}")
    int upgoods(String commodity_name, String commodity_description, double commodity_price, int id);

    //先把销量查出来
    @Select("select sales_volume from t_commodity where id=#{id}")
    T_commodity selxl(int id);

    //修改销量(订单为已发货调用一次该方法)
    @Update("update t_commodity set sales_volume=#{shuliang} where id=#{id} ")
    int upxl(int id, int shuliang);

    /*-------------------------------------根据规格手动修改商品价格库存----------------------------------------------*/
    @Update("update t_commodity a\n" +
            "inner join t_commodity_allocation b\n" +
            "on a.id= b.commodity_id and\n" +
            "b.attribute=#{attribute}\n" +
            "set b.price=#{price},b.stock=#{stock}\n" +
            "where a.id=#{id}")
    int updategg(String attribute, double price, int stock, int id);



    //先把库存查出来
    @Select("select stock from t_commodity_allocation where commodity_id=#{commodity_id} and attribute=#{attribute}")
    T_commodity_allocation selkc(int commodity_id,String attribute);

    //再修改库存
    @Update("update t_commodity_allocation set stock=#{shuliang} where commodity_id = #{commodity_id} and attribute=#{attribute}")
    int upkc(int commodity_id,String attribute,int shuliang);



    /*-----------------------------------------------上传图片所用方法--------------------------------------------------------*/
    //往单个图片表中添加图片
    @Insert("insert into t_picture(picture_path_name) values(#{picture_path_name})")
    int addpic(String picture_path_name);

    //查询图片表的图片id
    @Select("select id from t_picture where picture_path_name=#{picture_path_name}")
    int selpicid(String picture_path_name);

    //修改商品图片表的字段  根据id将picture_id 替换成 图片表的图片id
    @Update("update t_commodity_picture set picture_id=#{picture_id} " +  //图片
            "where commodity_id=#{commodity_id}")
    //根据商品id修改
    int upcom_pic(int picture_id, int commodity_id);

}
